package cn.sccl.common.database;


import cn.sccl.common.model.PageRes;
import cn.sccl.common.model.QueryData;
import cn.sccl.common.util.Const;
import cn.sccl.common.util.DataBaseUtils;
import cn.sccl.common.dao.impl.IBatisGenericDao;
import cn.sccl.common.model.Page;
import cn.sccl.common.model.PageResult;
import cn.sccl.common.util.JsonUtil;
import cn.sccl.common.util.PropertiesUtil;
import org.apache.commons.collections.OrderedMap;
import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 普通SQL的执行器
 * Created by 杨浩宇 on 2016-02-18.
 */
@Component
public class DataBase {
    private Log log = LogFactory.getLog(DataBase.class);

    @Resource(name = "iBatisGenericDao")
    private IBatisGenericDao iBatisGenericDao;

    /**
     * 关闭数据库的所有连接对象
     *
     * @param rs   数据集
     * @param stat java.sql.statment
     * @param conn 数据库连接
     */
    public static void closeAllOpenObject(ResultSet rs, Statement stat, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
            }
        }
        if (stat != null) {
            try {
                stat.close();
                stat = null;
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException e) {
            }
        }
    }

    /**
     * 判断sql查询语句中是否有*号。判断规则，在第一个from之前如果有*，则返回true，如果没有，返回false。
     *
     * @param strSQL
     * @return
     */
    public static boolean SqlNotContainsAsteriskChar(String strSQL) {
        int iFromPos = -1, iFindAsteriskPos = -1, iFromCurLen = 0;
        char cCurChar = ' ';
        for (int i = 0; i < strSQL.length(); i++) {
            cCurChar = strSQL.charAt(i);

            if (cCurChar == ' ') {
                iFromPos = i;
                iFromCurLen = 1;
            }

            if (cCurChar == 'f' || cCurChar == 'F') {
                if (i == iFromPos + 1) {
                    iFromCurLen = 2;
                } else {
                    iFromCurLen = 0;
                }
            }
            if ((cCurChar == 'r' || cCurChar == 'R')) {
                if (i == iFromPos + 2) {
                    iFromCurLen = 3;
                } else {
                    iFromCurLen = 0;
                }
            }
            if ((cCurChar == 'o' || cCurChar == 'O')) {
                if (i == iFromPos + 3) {
                    iFromCurLen = 4;
                } else {
                    iFromCurLen = 0;
                }
            }
            if ((cCurChar == 'm' || cCurChar == 'M')) {
                if (i == iFromPos + 4) {
                    iFromCurLen = 5;
                    break;
                } else {
                    iFromCurLen = 0;
                }
            }


            if (strSQL.charAt(i) == '*') {
                iFindAsteriskPos = i;
            }

        }
        //判断*的位置是否小于from的位置，包含*
        if (iFindAsteriskPos != -1 && iFromCurLen == 5 && iFindAsteriskPos < iFromPos)
            return false;
        else return true;

    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        SqlSessionTemplate st = (SqlSessionTemplate) iBatisGenericDao.getSqlSession();

        Connection connection = SqlSessionUtils.getSqlSession(
                st.getSqlSessionFactory(), st.getExecutorType(),
                st.getPersistenceExceptionTranslator()).getConnection();
        return connection;
    }

    /**
     * 查询单条记录到Map中
     *
     * @param sql
     * @param args
     * @return
     */
    public QueryData queryOneForMap(String sql, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        QueryData queryData = new QueryData();
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            rs = preparedStatement.executeQuery();
            queryData = DataSet.FillHashMap(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAllOpenObject(rs, preparedStatement, null);
        }
        return queryData;
    }

    /**
     * 查询返回数据集
     *
     * @param sql
     * @param args
     * @return
     */
    public DataSet query(String sql, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        DataSet ds = new DataSet();
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            rs = preparedStatement.executeQuery();
            ds = DataSet.FillDataSet(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAllOpenObject(rs, preparedStatement, null);
        }
        return ds;
    }

    /**
     * 分页查询，返回数据集
     *
     * @param sql
     * @param pageNo   页码
     * @param pageSize 每页大小
     * @param args
     * @return
     */
    public PageRes queryPageListMap(String sql, long pageNo, long pageSize, Object... args) {
        String countSql = DataBaseUtils.getCountSQL(sql);
        String pagedSQL = DataBaseUtils.getPagedSQL(sql, pageNo, pageSize);
        PageRes page = new PageRes();
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        long count=0;
        List res = new ArrayList();
        try {
            preparedStatement = initPrepareStatement(connection, countSql, args);
            rs = preparedStatement.executeQuery();
            if(rs != null && rs.next()){
                count = rs.getLong(1);
            }
            rs.close();
            preparedStatement.close();

            preparedStatement = initPrepareStatement(connection, pagedSQL, args);
            rs = preparedStatement.executeQuery();
            if(rs != null){
                int colCount = rs.getMetaData().getColumnCount();
                while (rs.next()){
                    Map<String,Object> map = new ListOrderedMap();
                    for(int i=1;i<=colCount;i++){
                        map.put(rs.getMetaData().getColumnName(i),rs.getObject(i));
                    }
                    res.add(map);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAllOpenObject(rs, preparedStatement, null);
        }
        page.setPage(pageNo);
        page.setPageSize(pageSize);
        page.setTotal(count);
        page.setRows(res);
        return page;
    }
    /**
     * 统计个数
     *
     * @param sql
     * @param args
     * @return
     */
    public long queryCount(String sql, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        long count=0;
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            rs = preparedStatement.executeQuery();
            if(rs != null && rs.next()){
                count = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAllOpenObject(rs, preparedStatement, null);
        }
        return count;
    }
    /**
     * 查询返回数据集
     *
     * @param sql
     * @param args
     * @return
     */
    public List queryList(String sql, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List res = new ArrayList();
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            rs = preparedStatement.executeQuery();
            if(rs != null){
                int colCount = rs.getMetaData().getColumnCount();
                while (rs.next()){
                    Map<String,Object> map = new ListOrderedMap();
                    for(int i=1;i<=colCount;i++){
                        map.put(rs.getMetaData().getColumnName(i),rs.getObject(i));
                    }
                    res.add(map);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAllOpenObject(rs, preparedStatement, null);
        }
        return res;
    }

    /**
     * 分页查询，返回数据集
     *
     * @param sql
     * @param pageNo   页码
     * @param pageSize 每页大小
     * @param args
     * @return
     */
    public DataSet query(String sql, long pageNo, long pageSize, Object... args) {
        String pagedSQL = DataBaseUtils.getPagedSQL(sql, pageNo, pageSize);
        return query(sql, args);
    }
    /**
     * 执行sql，并返回对象列表
     *
     * @param sql
     * @param entityClass
     * @param args
     * @param <T>
     * @return
     */
    public <T> List<T> query4Model(String sql, Class<T> entityClass, Object... args) {
        Boolean bStrictMaching = SqlNotContainsAsteriskChar(sql);
        return query4Model(sql, entityClass, bStrictMaching, args);
    }

    /**
     * 分页查询
     *
     * @param sql
     * @param pageNo
     * @param pageSize
     * @param entityClass
     * @param args
     * @param <T>
     * @return
     */
    public <T> PageResult<T> query4Model(String sql, long pageNo, int pageSize, Class<T> entityClass, Object... args) {
        String countSQL = DataBaseUtils.getCountSQL(sql);
        QueryData queryData = this.queryOneForMap(countSQL, args);
        // for oracle
        if (PropertiesUtil.getString("jdbc.type").equals(Const.ORACLE)) {
            BigDecimal total = (BigDecimal) queryData.get("TOTAL");
            String pagedSQL = DataBaseUtils.getPagedSQL(sql, pageNo, pageSize);
            List<T> result = query4Model(pagedSQL, entityClass, args);
            Page resultPage = Page.newInstance(pageNo, pageSize);
            resultPage.setTotal(total.longValue());
            resultPage.addAll(result);//结果直接增加进去
            return new PageResult<T>(resultPage);
        } else {
            // for mysql
            Long total = (Long) queryData.get("total");
            String pagedSQL = DataBaseUtils.getPagedSQL(sql, pageNo, pageSize);
            List<T> result = query4Model(pagedSQL, entityClass, args);
            Page resultPage = Page.newInstance(pageNo, pageSize);
            resultPage.setTotal(total.longValue());
            resultPage.addAll(result);//结果直接增加进去
            return new PageResult<T>(resultPage);
        }
    }

    public boolean execute(String sql, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        boolean result = false;
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            result = preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAllOpenObject(null, preparedStatement, null);
        }
        return result;
    }

    public int executeUpdate(String sql, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        int result = 0;
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAllOpenObject(null, preparedStatement, null);
        }
        return result;
    }

    /**
     * @param sql
     * @param entityClass
     * @param bStrictMaching
     * @param args
     * @param <T>
     * @return
     */
    private <T> List<T> query4Model(String sql, Class<T> entityClass, Boolean bStrictMaching, Object... args) {
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<T> list = null;
        try {
            preparedStatement = initPrepareStatement(connection, sql, args);
            rs = preparedStatement.executeQuery();
            list = DataBaseUtils.FillModel(entityClass, rs, bStrictMaching);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAllOpenObject(rs, preparedStatement, null);
        }
        if (list == null) {
            list = new ArrayList<T>();
        }
        return list;
    }

    /**
     * @param conn   数据库链接
     * @param strSQL SQL语句
     * @param args   SQL参数
     * @throws SQLException
     * @Title: initPrepareStatement
     * @Description: 初始化PrepareStatement参数
     * @Author: bieby
     * @Date: 2014-1-21
     */
    public PreparedStatement initPrepareStatement(Connection conn, String strSQL, Object... args) throws SQLException {
        PreparedStatement stat = null;
        // 打印查询语句
        log.debug("DataBase准备执行的SQL语句：" + strSQL + " ; 参数：" + JsonUtil.toJSONString(args));

        //针对模糊查询，预先格式化
        strSQL = strSQL.replaceAll("'%\\?%'", "'%'||?||'%'");

        // 查询
//		stat = conn.prepareStatement(strSQL,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
        stat = conn.prepareStatement(strSQL);
        if (args != null) {
            for (int index = 0; index < args.length; index++) {
                stat.setObject(index + 1, args[index]);
            }
        }
        return stat;
    }
}
